import pymysql
import pandas as pd
import numpy as np

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )

    def __del__(self):
        self.conn.close()

    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT o.tourist_agency_name, u.id_no, LEFT(u.id_no, 2) AS province_code,
               DAYOFWEEK(gate.create_time) AS non_weekend,
               CAST(SUBSTRING(u.id_no, 7, 4) AS UNSIGNED) AS birth_year
        FROM ticket_order_user_rel u 
        JOIN ticket_order o ON u.order_id = o.id 
        JOIN order_user_gate_rel gate ON gate.ticket_rel_id = u.id
        WHERE o.tourist_agency_name != '' AND o.pay_time != ''
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)

        # 标记是否为非周末
        df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)

        # 标记是否为有效身份证号
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if str(x).strip() != '' else 0)

        # 标记是否为老年人（假设60岁以上）
        current_year = 2025  # 假设当前年份为2025
        df['elderly_ratio'] = df.apply(
            lambda x: 1 if x['valid_id'] == 1 and (current_year - x['birth_year']) >= 60 else 
            0 if x['valid_id'] == 1 else np.nan,
            axis=1
        )

        # 标记是否为外省游客（假设本地省份代码为'44'）
        df['out_province_ratio'] = df.apply(
            lambda x: 1 if x['valid_id'] == 1 and x['province_code'] != '44' else 
            0 if x['valid_id'] == 1 else np.nan,
            axis=1
        )

        # 分组聚合计算占比
        result = df.groupby(['tourist_agency_name']).agg(
            total_visitors=('id_no', 'count'),  # 总游客数
            valid_visitors=('valid_id', 'sum'),  # 有效身份证游客数
            out_province=('out_province_ratio', 'sum'),  # 外省人数（仅有效id_no）
            elderly=('elderly_ratio', 'sum'),  # 老年人人数（仅有效id_no）
            non_weekend_ratio=('non_weekend', 'mean')  # 非周末游客占比（仅有效id_no）
        ).reset_index()

        # 计算实际比例
        result['out_province_ratio'] = result['out_province'] / result['valid_visitors'].replace(0, np.nan)
        result['elderly_ratio'] = result['elderly'] / result['valid_visitors'].replace(0, np.nan)

        # 清除中间列
        result = result.drop(['out_province', 'elderly'], axis=1)

        # 填充 NaN 值
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)

        print(result.head())
        result.to_csv('scenic_data.csv')

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()